package com.develope.tree.db;

import java.util.ArrayList;

import com.develope.tree.Tree;
import com.develope.tree.UserParcel;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class dbUserParcel extends SQLiteOpenHelper {
	public static final String DATABASE_NAME = "MyDB.db";
	public static final String DATA_TABLE_NAME = "userData";
	public static final int DATABASE_VERSION = 1;
	
	public static final String DATA_COLUMN_ID = "id";
	public static final String DATA_COLUMN_USER = "User";
	public static final String DATA_COLUMN_PARCELID = "parcelId";
	public static final String DATA_COLUMN_PARCELNAME = "parcelName";
	
	public dbUserParcel(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);

	}
	
	@Override
	public void onCreate(SQLiteDatabase db){
		// TODO Auto-gennerated method stud
		db.execSQL("create table userData"
				+ "(id integer primary key, User text, parcelId int, parcelName text, FOREIGN KEY(User, ParcelId))");
			
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS userData");
		this.onCreate(db);
	}
	
	public boolean insertUser(UserParcel user) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		
		contentValues.put("User", user.getUserName());
		contentValues.put("parcelId", user.getParcelID());
		contentValues.put("parcelName", user.getParcelName());
		
		db.insert("userData", null, contentValues);
		return true;
	}
	
	public UserParcel getUser(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		UserParcel user = new UserParcel();
		Cursor res = db
				.rawQuery("select * from userData where id like '" + id + "'", null);
		if (res != null) {
			res.moveToFirst();
		}
		user.setUserName(res.getString(res.getColumnIndex(dbUserParcel.DATA_COLUMN_USER)));
		user.setParcelID(res.getInt(res.getColumnIndex(dbUserParcel.DATA_COLUMN_PARCELID)));
		user.setParcelName(res.getString(res.getColumnIndex(dbUserParcel.DATA_COLUMN_PARCELNAME)));
		
		return user;
	}
	
	public int numberOfRows() {
		SQLiteDatabase db = this.getReadableDatabase();
		int numRows = (int) DatabaseUtils.queryNumEntries(db, DATA_TABLE_NAME);
		return numRows;
	}
	
	public boolean updateUser(UserParcel user) {
		SQLiteDatabase db = this.getWritableDatabase();
//		SQLiteDatabase db = this.getReadableDatabase();
		
		ContentValues contentValues = new ContentValues();
		
		contentValues.put("User", user.getUserName());
		contentValues.put("parcelId", user.getParcelID());
		contentValues.put("parcelName", user.getParcelName());
		
		db.update("userData", contentValues, "User = ? and parcelId = ?",
				new String[] { user.getUserName().toString(), Integer.toString(user.getParcelID()) });
		return true;
	}
	
	public Integer deleteUser(UserParcel user) {
		SQLiteDatabase db = this.getWritableDatabase();
		return db.delete(DATA_TABLE_NAME, "User = ? and parcelId = ?",
				new String[] { user.getUserName().toString(),Integer.toString(user.getParcelID()) });
	}
	
	public ArrayList<UserParcel> getAllParcel(String user) {
		ArrayList<UserParcel> listParcel = new ArrayList<UserParcel>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor res = db.rawQuery("select * from userData where "+DATA_COLUMN_USER+" = ?", new String[] {user});
		if (res.moveToFirst()) {
			do {
				UserParcel up=new UserParcel();
				up.setUserName(res.getString(res.getColumnIndex(dbUserParcel.DATA_COLUMN_USER)));
				up.setParcelID(res.getInt(res.getColumnIndex(dbUserParcel.DATA_COLUMN_PARCELID)));
				up.setParcelName(res.getString(res.getColumnIndex(dbUserParcel.DATA_COLUMN_PARCELNAME)));			

				listParcel.add(up);
			} while (res.moveToNext());
		}
		return listParcel;
	}
	public void comPare2(ArrayList<UserParcel> arr, String mUser) {
		
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor res = db.rawQuery("select * from "+DATA_TABLE_NAME+" where "+DATA_COLUMN_USER+" = ?", new String[] { mUser});
		if (res.moveToFirst()) {
			do {
				UserParcel user=new UserParcel();
				user.setUserName(res.getString(res.getColumnIndex(dbUserParcel.DATA_COLUMN_USER)));
				user.setParcelID(res.getInt(res.getColumnIndex(dbUserParcel.DATA_COLUMN_PARCELID)));
				user.setParcelName(res.getString(res.getColumnIndex(dbUserParcel.DATA_COLUMN_PARCELNAME)));
				Boolean exist = false;
				for (int i=0;i<arr.size();i++){
				if(arr.get(i).getUserName().equals(user.getUserName()) && arr.get(i).getParcelID()==user.getParcelID() && arr.get(i).getParcelName().equals(user.getParcelName())){
					exist = true;
					break;
				}
				
//				listUser.add(user);
			}
				if (!exist){
					deleteUser(user);
				}
				
			}
			while (res.moveToNext());
		}
		return;
	}

	public void comPare1(UserParcel up){
		SQLiteDatabase db = this.getWritableDatabase();
		
		Cursor c = db.rawQuery("select * from "+DATA_TABLE_NAME+" where "+DATA_COLUMN_USER+" = ? and "+DATA_COLUMN_PARCELID+" = ?", new String[] { up.getUserName().toString(), Integer.toString(up.getParcelID())});
		if(c.getCount()==0){
			insertUser(up);
			c.close();
			return;
		} else {
			updateUser(up);
			c.close();
			return;
			}
	}
}